package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Properties;
import java.util.TimeZone;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.joda.time.DateTime;

public class Mhrmsettax extends X_hrm_set_tax {

	
	/**
	 * 
	 */
	private static final long serialVersionUID = -5047465671263238211L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmsettax.class);
	
	/**
	 * @param ctx
	 */
	public Mhrmsettax(Properties ctx) {
		super(ctx);
		// TODO Auto-generated constructor stub
	}

	/**
	 * @param ctx
	 * @param hrm_set_tax_ID
	 * @param trxName
	 */
	public Mhrmsettax(Properties ctx, int hrm_set_tax_ID, String trxName) {
		super(ctx, hrm_set_tax_ID, trxName);
		// TODO Auto-generated constructor stub
	}
	
	/**
	 * @param ctx
	 * @param rs
	 * @param trxName
	 */
	public Mhrmsettax(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
		// TODO Auto-generated constructor stub
	}

public static BigDecimal getRateValue(String type, Timestamp dt){		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BigDecimal rate = new BigDecimal(0);
		
		// Employee data from the card
		String sql_ = "SELECT RATE FROM HRM_SET_TAX \n " +
						" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						  " AND " + DB.TO_DATE(dt, true) + " BETWEEN STARTDATE AND ENDDATE \n " +
						  " AND HRM_TAXTYPE='" + type + "' ";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();
			if(rs.next())
				return rate = rs.getBigDecimal(1);			
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return rate;
	}

	/* (non-Javadoc)
	 * @see org.compiere.model.PO#beforeSave(boolean)
	 */
	@Override
	protected boolean beforeSave(boolean newRecord) {
				
		Mhrmsettax mindt = null;
		Mhrmsettax maxdt = null;
		
		Timestamp dt = null;		
		Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("GMT"));	
		
		Timestamp sdt = getStartDate();
		Timestamp edt = getEndDate();
		
		if (getStartDate().after(getEndDate()))
			throw new AdempiereException (" StartDate not must be after EndDate. ");
		
		if (newRecord){
			mindt = getMinStartDate(gethrm_taxtype());
			maxdt = getMaxEndDate(gethrm_taxtype());
		
			calendar.setTime(sdt);
			calendar.add(Calendar.DAY_OF_MONTH, -1);			
			dt = new Timestamp(calendar.getTimeInMillis());
			
			if (edt.before(mindt.getStartDate())){				
				setIsActive(false);
			}else if (sdt.after(maxdt.getEndDate())){
				maxdt.setEndDate(dt);
				maxdt.setIsActive(false);
				maxdt.saveEx();
			}else{
				throw new AdempiereException (" Please, select correct date. Hint. New records must be after old records. ");
			}				
		}
		
		return super.beforeSave(newRecord);
	}		
		
	 private static Mhrmsettax getMinStartDate(String taxtype){
		 
	    	if(taxtype == "")
	    		return null;	
	    	
	    	Mhrmsettax result = null;	    	
	    	PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			// Employee data from the card
					String sql_ = "SELECT * FROM HRM_SET_TAX \n " +
									" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
									" AND HRM_SET_TAX.HRM_TAXTYPE='" + taxtype + "' \n     " +
							        " AND HRM_SET_TAX.STARTDATE = (SELECT MIN(H.STARTDATE) " +
							        " FROM HRM_SET_TAX H WHERE H.HRM_TAXTYPE=HRM_SET_TAX.HRM_TAXTYPE) " ;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();
				if(rs.next())
					result = new Mhrmsettax(Env.getCtx(), rs, null);			
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			return result;	
	    }
	 
	 private static Mhrmsettax getMaxEndDate(String taxtype){
		 
	    	if(taxtype == "")
	    		return null;	
	    	
	    	Mhrmsettax result = null;	    	
	    	PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			// Employee data from the card
					String sql_ = "SELECT * FROM HRM_SET_TAX \n " +
									" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
									" AND HRM_SET_TAX.HRM_TAXTYPE='" + taxtype + "' \n " +
							        " AND HRM_SET_TAX.ENDDATE = (SELECT MAX(H.ENDDATE) \n " +
							        " FROM HRM_SET_TAX H WHERE H.HRM_TAXTYPE=HRM_SET_TAX.HRM_TAXTYPE) \n ";
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();
				if(rs.next())
					result = new Mhrmsettax(Env.getCtx(), rs, null);			
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			return result;	
	    }
}
